/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ru.staf.NetworkReportGenerator;

import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.io.File;
import java.io.IOException;
import jxl.*;
import jxl.write.*;
import java.util.Iterator;

import java.util.HashSet;

/**
 *
 * @author aezhov
 */
public class ExcelReportDrawer {
    public ExcelReportDrawer(LinkedHashMap<Switch, LinkedList> tableToDraw) 
            throws java.io.IOException, jxl.write.WriteException
    {
        WritableWorkbook workbook = Workbook.createWorkbook(new File("/home/aezhov/netrep.xls"));
        WritableSheet sheet = workbook.createSheet("OpenNMS report", 0);
        Iterator<Switch> swIt = tableToDraw.keySet().iterator();
        int posY = 0;

        WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        WritableFont hl1_Font = new WritableFont(WritableFont.ARIAL, 10);
        hl1_Font.setColour(jxl.format.Colour.BLUE);

        WritableCellFormat centerCell = new WritableCellFormat(headerFont);
        centerCell.setAlignment(jxl.format.Alignment.CENTRE);

        WritableCellFormat stdCell = new WritableCellFormat();
        stdCell.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        WritableCellFormat stdCellGrey = new WritableCellFormat();
        stdCellGrey.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        stdCellGrey.setBackground(jxl.format.Colour.YELLOW);

        WritableCellFormat stdCell_HL1 = new WritableCellFormat(hl1_Font);
        stdCell_HL1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        WritableCellFormat stdCellGrey_HL1 = new WritableCellFormat(hl1_Font);
        stdCellGrey_HL1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        stdCellGrey_HL1.setBackground(jxl.format.Colour.YELLOW);

        while (swIt.hasNext()) {
            Switch sw = swIt.next();
            Label labelH = new Label(0, posY, sw.name.toUpperCase(), centerCell);
            sheet.mergeCells(0, posY, 15, posY);
            sheet.addCell(labelH);

            posY++;
            
            Label labelh1 = new Label(2, posY, "Мегабит/сек ВХОД", centerCell);
            Label labelh2 = new Label(5, posY, "Мегабит/сек ВЫХОД", centerCell);
            Label labelh3 = new Label(8, posY, "Ошибок/сек", centerCell);
            Label labelh4 = new Label(10, posY, "Отказ обсл./сек", centerCell);

            sheet.mergeCells(2, posY, 4, posY);
            sheet.mergeCells(5, posY, 7, posY);
            sheet.mergeCells(8, posY, 9, posY);
            sheet.mergeCells(10, posY, 11, posY);

            posY++;

            Label labelh5 = new Label(0, posY, "Порт", centerCell);
            Label labelh55 = new Label(1, posY, "Ск. подкл.", centerCell);
            Label labelh6 = new Label(2, posY, "AVG", centerCell);
            Label labelh7 = new Label(3, posY, "MAX", centerCell);
            Label labelh8 = new Label(4, posY, "MIN", centerCell);
            Label labelh9 = new Label(5, posY, "AVG", centerCell);
            Label labelh10 = new Label(6, posY, "MAX", centerCell);
            Label labelh11 = new Label(7, posY, "MIN", centerCell);
            Label labelh12 = new Label(8, posY, "Вход", centerCell);
            Label labelh13 = new Label(9, posY, "Выход", centerCell);
            Label labelh14 = new Label(10, posY, "Вход", centerCell);
            Label labelh15 = new Label(11, posY, "Выход", centerCell);
            Label labelhMAC = new Label(12, posY, "MAC addr", centerCell);
            Label labelhIP = new Label(13, posY, "IP addr", centerCell);
            Label labelhVendor = new Label(14, posY, "Вендор", centerCell);
            Label labelhModel = new Label(15, posY, "Модель", centerCell);

            CellView cv = new CellView();
            cv.setAutosize(true);
            sheet.setColumnView(12, cv);
            sheet.setColumnView(13, cv);
            sheet.setColumnView(14, cv);
            sheet.setColumnView(15, cv);
            sheet.setColumnView(1, cv);

            sheet.addCell(labelh1);
            sheet.addCell(labelh2);
            sheet.addCell(labelh3);
            sheet.addCell(labelh4);
            sheet.addCell(labelh5);
            sheet.addCell(labelh55);
            sheet.addCell(labelh6);
            sheet.addCell(labelh7);
            sheet.addCell(labelh8);
            sheet.addCell(labelh9);
            sheet.addCell(labelh10);
            sheet.addCell(labelh11);
            sheet.addCell(labelh12);
            sheet.addCell(labelh13);
            sheet.addCell(labelh14);
            sheet.addCell(labelh15);
            sheet.addCell(labelhMAC);
            sheet.addCell(labelhIP);
            sheet.addCell(labelhVendor);
            sheet.addCell(labelhModel);
            
            posY++;

            LinkedList<ReportTableMaker.Record> recs = tableToDraw.get(sw);
            Iterator<ReportTableMaker.Record> itRec = recs.iterator();
            WritableCellFormat currentFormat = stdCellGrey;
            WritableCellFormat currentFormat_HL1 = stdCellGrey_HL1;

            while (itRec.hasNext()) {
                ReportTableMaker.Record rec = itRec.next();
                Label lPortName = new Label(0, posY, rec.portName, currentFormat);
                Label lLinkSpeed = new Label(1, posY, rec.linkSpeed, currentFormat);
                Label lOctInAVG = new Label(2, posY, toMbitsStr(rec.bytesInAvg), currentFormat);
                Label lOctInMAX = new Label(3, posY, toMbitsStr(rec.bytesInMax), currentFormat_HL1);
                Label lOctInMIN = new Label(4, posY, toMbitsStr(rec.bytesInMin), currentFormat);
                Label lOctOutAVG = new Label(5, posY, toMbitsStr(rec.bytesOutAvg), currentFormat);
                Label lOctOutMAX = new Label(6, posY, toMbitsStr(rec.bytesOutMax), currentFormat_HL1);
                Label lOctOutMIN = new Label(7, posY, toMbitsStr(rec.bytesOutMin), currentFormat);
                Label lErrIn = new Label(8, posY, toPacketStr(rec.errorsIn), currentFormat);
                Label lErrOut = new Label(9, posY, toPacketStr(rec.errorsOut), currentFormat);
                Label lDiscardIn = new Label(10, posY, toPacketStr(rec.discardsIn), currentFormat);
                Label lDiscardOut = new Label(11, posY, toPacketStr(rec.discardsOut), currentFormat);
                Label lMAC = new Label(12, posY, "n/a", currentFormat);
                Label lIP = new Label(13, posY, "n/a", currentFormat_HL1);
                Label lVendor = new Label(14, posY, "n/a", currentFormat);
                Label lModel = new Label(15, posY, "n/a", currentFormat);
                
                String mac = null;
                String ip = null;

                HashSet<String> macs = rec.macs;
                if (macs != null) {
                    if (macs.size() == 1) {
                        mac = macs.iterator().next();
                        lMAC.setString(mac);
                    } else {
                        lMAC.setString("TRUNK TO...");
                    }
                }

                HashSet<String> ips = rec.ips;
                if (ips != null) {
                    if (ips.size() == 1) {
                        ip = ips.iterator().next();
                        lIP.setString(ip);

                        if (mac != null) {
                            /*DeviceRegistry registry = DeviceRegistry.getRegistry();
                            DeviceFactory df = registry.getDeviceFactory(mac);
                            if (df != null) {
                                Device dev = df.getDeviceInfo(mac, ip);
                                if (dev != null) {
                                    lVendor.setString(dev.getVendor());
                                    lModel.setString(dev.getModel());
                                }
                            }*/
                        }
                    }
                }

                posY++;

                sheet.addCell(lPortName);
                sheet.addCell(lLinkSpeed);
                sheet.addCell(lOctInAVG);
                sheet.addCell(lOctInMAX);
                sheet.addCell(lOctInMIN);
                sheet.addCell(lOctOutAVG);
                sheet.addCell(lOctOutMAX);
                sheet.addCell(lOctOutMIN);
                sheet.addCell(lErrIn);
                sheet.addCell(lErrOut);
                sheet.addCell(lDiscardIn);
                sheet.addCell(lDiscardOut);
                sheet.addCell(lMAC);
                sheet.addCell(lIP);
                sheet.addCell(lVendor);
                sheet.addCell(lModel);
                
                if (currentFormat == stdCellGrey) {
                    currentFormat = stdCell;
                    currentFormat_HL1 = stdCell_HL1;
                } else {
                    currentFormat = stdCellGrey;
                    currentFormat_HL1 = stdCellGrey_HL1;
                }
            }

            posY += 2;
        }

        workbook.write();
        workbook.close();
    }

    String toMbitsStr(double valueInBytes) {
        double mbits = (valueInBytes * 8) / 1e6;
        String result = String.format(" %.3f ", mbits);
        return result;
    }

    String toPacketStr(double packets) {
        if (packets < 0)
            return "n/a";
        
        String result = String.format(" %.2f", packets);
        return result;
    }
}
